package resources.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.faces.model.SelectItem;
import org.primefaces.model.DefaultTreeNode;
import org.primefaces.model.TreeNode;

import resources.pojo.pe_deptmt;
import resources.util.Tool;

public class DepartmentDAO extends DatabaseDAO{
	
	public static TreeNode getDepartmentTree(){
		TreeNode root = null;
		try{
			root = new DefaultTreeNode("Root", null);
			conn.setAutoCommit(false);
			repeatTree(root,0);
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return root;
	}
	
	public static void repeatTree(TreeNode node, int id){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			pe_deptmt object = null;
			String query = "select * from pe_deptmt where partid = ?";
			ps = conn.prepareStatement(query);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				TreeNode sub;
				object = new pe_deptmt();
				object.setId(rs.getInt("id"));
				object.setKywd(rs.getString("kywd"));
				object.setPartid(rs.getInt("partid"));
				object.setDscptn(rs.getString("dscptn"));
				sub = new DefaultTreeNode("document", object , node); 
				sub.setExpanded(true);
				repeatTree(sub,object.getId());
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)rs.close();
				if(ps != null){ps.close();}
			}catch (Exception e) {e.printStackTrace(System.out);}
		}
	}
	
	public static ArrayList<pe_deptmt> getSelectDepartmentManage(){
		ArrayList<pe_deptmt> listTemp = new ArrayList<pe_deptmt>();
		String sql = "select * from pe_deptmt";
		pe_deptmt object = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				object = new pe_deptmt();
				object.setId(rs.getInt("id"));
				object.setKywd(rs.getString("kywd"));
				object.setPartid(rs.getInt("partid"));
				listTemp.add(object);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return listTemp;
	}
	
	public static ArrayList<SelectItem> getSelectDepartment(){
		ArrayList<SelectItem> listTemp = new ArrayList<SelectItem>();
		String sql="select * from pe_deptmt";
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				listTemp.add(new SelectItem(rs.getInt("id"),rs.getString("kywd")));
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
		return listTemp;
	}
	
	public static boolean updateDepartment(pe_deptmt object){
		int row = 0;
		try{
			String query = "update pe_deptmt set kywd = ?, partid = ?, dscptn = ? where id = ?";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			ps.setString(1, object.getKywd());
			ps.setInt(2, object.getPartid());
			ps.setString(3, object.getDscptn());
			ps.setInt(4, object.getId());
			row = ps.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static boolean insertDepartment(pe_deptmt object){
		int row = 0;
		try{
			String query = "insert pe_deptmt(kywd,partid,dscptn) values(?,?,?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			ps.setString(1, object.getKywd());
			ps.setInt(2, object.getPartid());
			ps.setString(3, object.getDscptn());
			row = ps.executeUpdate();
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row > 0;
	}
	
	public static int deleteDepartment(int id){
		int row = 0;
		try{
			String query = "select partid from pe_deptmt where partid = ?";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(query);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				row = 0; // department nay co chua department con
			}else{
				query = "select deptmt from pe_eplye where deptmt = ?";
				ps = conn.prepareStatement(query);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				if(rs.next()){
					row = 1; // department nay co chua nhan vien, ko the delete dc
				}else{
					query = "delete from pe_deptmt where id = ?";
					ps = conn.prepareStatement(query);
					ps.setInt(1, id);
					row = ps.executeUpdate();
					if(row > 0){
						row = 2; // da kiem tra 2 thang tren nen co the delete
					}
				}
			}
			conn.commit();
		}catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace(System.out);
		}
		return row;
	}
}
